How to create and use View in DB2 for i SQL

How to create and use View in DB2 for i SQL

A view is similar to Non-keyed LF and is used to access data in one or more table or views. We can create view by using the SELECT statement.

For example, Create a view by selecting all fields of file PF1.

CREATE VIEW VIEW1LONGNAME FOR SYSTEM NAME VIEW1 AS SELECT *  FROM PF1

Once the above CREATE VIEW query executed a view named VIEW1LONGNAME will be created and whose system object name will be VIEW1 using the clause FOR SYSTEM NAME. Without adding FOR SYSTEM NAME clause, system will generate the name like VIEW100001. We can do WRKOBJ on VIEW1 to search for this object. We can also change the data in table PF1 using view VIEW1.

Select * from VIEW1LONGNAME

Or

Select * from VIEW1

would give me the data of the VIEW1 based upon table PF1

 EMPID       EMPNAME               MANAGERID 
 E1          EMPNAME1              M1        
 E2          EMPNAME2              M2        

If the SELECT list contains elements other than columns such as expressions, functions, constants, or special registers and the AS clause was not used to name the columns, a column list must be specified for the view.

CREATE VIEW VIEW1LONGNAME FOR SYSTEM NAME VIEW1 (ID, EMPNAME) AS
SELECT EMPID concat '-' concat managerid, empname FROM pf1      

Or

CREATE VIEW VIEW1LONGNAME FOR SYSTEM NAME VIEW1 AS SELECT EMPID 
concat '-' concat managerid as id, empname FROM pf1             

Select * from VIEW1LONGNAME

Or

Select * from VIEW1

would give me the data of the VIEW1 based upon table PF1

ID                     EMPNAME 
E1        -M1          EMPNAME1
E2        -M2          EMPNAME2

Create view by combining two or more sub-select query

We can create view by using the UNION keyword to combine results of two or more sub-select query and form a single view.

CREATE VIEW VIEW1LONGNAME  for system name VIEW1 as 
  (SELECT *                                         
     FROM PF1                                       
     WHERE EMPID ='E1'                              
   UNION                                            
   SELECT *                                         
     FROM PF1B                                      
     WHERE MANAGERID = 'M1' )